﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.SqlServer.Server;

namespace DataCenter.Share
{
    public class TableSetting
    {
        public const string DEFAULT_TABLE_NAME = "DataTable";

        private TableSetting()
        {
            
        }
        private static TableSetting Instance = null;
        public static TableSetting CreateInstance()
        {
            if (Instance == null)
            {
                Instance = new TableSetting();
                Instance.Initialize();
            }
            return Instance;
        }
        public static TableSetting GetInstance()
        {
            if (Instance == null)
                Instance = new TableSetting();
            return Instance;
        }

        private string tableName = DEFAULT_TABLE_NAME;
        private int splitMonthStep = 1;
        private int startTableIndex = 0;
        private int endTableIndex = 0;

        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }

        public int SplitMonthStep
        {
            get { return splitMonthStep; }
            set { splitMonthStep = value; }
        }

        public int StartTableIndex
        {
            get { return startTableIndex; }
            set { startTableIndex = value; }
        }

        public int EndTableIndex
        {
            get { return endTableIndex; }
            set { endTableIndex = value; }
        }

        public void Initialize()
        {
            using (SqlConnection connection = new SqlConnection("context connection = true"))
            {
                connection.Open();
                using (SqlCommand command = connection.CreateCommand())
                {
                    Initialize(command);
                }
            }
        }
        public void Initialize(SqlCommand command)
        {
            Stopwatch watch = new Stopwatch();
            watch.Start();

            string commandString = "SELECT * FROM TableIndexSetting";
            command.CommandText = commandString;
            command.CommandType = CommandType.Text;
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    object oValue = reader["TableName"];
                    if (oValue != DBNull.Value && !string.IsNullOrEmpty(oValue.ToString()))
                        tableName = oValue.ToString();

                    int iValue;

                    oValue = reader["SplitMonthStep"];
                    if (oValue != DBNull.Value && !string.IsNullOrEmpty(oValue.ToString()))
                    {
                        if (int.TryParse(oValue.ToString(), out iValue))
                            if (iValue > 0) splitMonthStep = iValue;
                    }

                    oValue = reader["StartTableIndex"];
                    if (oValue != DBNull.Value && !string.IsNullOrEmpty(oValue.ToString()))
                    {
                        if (int.TryParse(oValue.ToString(), out iValue))
                            if (iValue >= 0) startTableIndex = iValue;
                    }

                    oValue = reader["EndTableIndex"];
                    if (oValue != DBNull.Value && !string.IsNullOrEmpty(oValue.ToString()))
                    {
                        if (int.TryParse(oValue.ToString(), out iValue))
                            if (iValue >= 0) endTableIndex = iValue;
                    }
                }
            }

            watch.Stop();
            if (SqlContext.IsAvailable)
                SqlContext.Pipe.Send("TableSetting: " + watch.ElapsedMilliseconds);
        }
        public void UpdateIndex(SqlCommand command)
        {
            string commandString = @"UPDATE TableIndexSetting SET StartTableIndex = @StartTableIndex, EndTableIndex = @EndTableIndex 
                                    WHERE TableName = @TableName";
            command.CommandText = commandString;
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@StartTableIndex", startTableIndex);
            command.Parameters.AddWithValue("@EndTableIndex", endTableIndex);
            command.Parameters.AddWithValue("@TableName", tableName);            
            if (command.ExecuteNonQuery() == 0)
            {
                command.Parameters.Clear();
                Insert(command);
            }            
            Console.WriteLine("StartTableIndex=" + startTableIndex + ";EndTableIndex=" + endTableIndex);
        }
        public void Update(SqlCommand command)
        {
            string commandString = @"UPDATE TableIndexSetting SET TableName = @TableName, SplitMonthStep = @SplitMonthStep";
            command.CommandText = commandString;
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@TableName", TableName);
            command.Parameters.AddWithValue("@SplitMonthStep", splitMonthStep);            
            if (command.ExecuteNonQuery() == 0)
            {
                command.Parameters.Clear();
                Insert(command);
            }
        }
        public void Insert(SqlCommand command)
        {
            string commandString = @"INSERT INTO TableIndexSetting(TableName, SplitMonthStep, StartTableIndex, EndTableIndex)
                                     VALUES(@TableName, @SplitMonthStep, @StartTableIndex, @EndTableIndex)";
            command.CommandText = commandString;
            command.Parameters.AddWithValue("@TableName", tableName);
            command.Parameters.AddWithValue("@SplitMonthStep", splitMonthStep);
            command.Parameters.AddWithValue("@StartTableIndex", startTableIndex);
            command.Parameters.AddWithValue("@EndTableIndex", endTableIndex);
            command.ExecuteNonQuery();
            command.Parameters.Clear();
        }
        public int GetTableIndex(DateTime valueTime)
        {
            return GetTimeIndex(valueTime);
        }
        public string GetTableName(DateTime valueTime) 
        {
            int tableIndex = GetTableIndex(valueTime);
            return GetTableName(tableName, tableIndex);
        }
        public string GetTableName(int tableIndex)
        {
            return GetTableName(tableName, tableIndex);
        } 
        public bool IsTableExist(DateTime valueTime) 
        {
            if (startTableIndex == 0 && endTableIndex == 0)
                return false;

            int timeIndex = GetTimeIndex(valueTime);
            if (timeIndex < startTableIndex) return false;
            if (timeIndex > endTableIndex) return false;
            return true;
        }
        public bool AdjustTimeRegion(DateTime startTime, DateTime endTime, ref int startIndex, ref int endIndex) 
        {
            if (startTableIndex == 0 && endTableIndex == 0)
                return false;
           
            startIndex = GetTimeIndex(startTime);
            endIndex = GetTimeIndex(endTime);

            if (endIndex < startTableIndex && startIndex < startTableIndex)
                return false;
            if (startIndex > endTableIndex && endIndex > endTableIndex)
                return false;

            if (startIndex < startTableIndex) startIndex = startTableIndex;
            if (endIndex > endTableIndex) endIndex = endTableIndex;

            return true;
        }
        public void CreateTable(SqlCommand command, int tableIndex)
        {
            string fullName = GetTableName(tableIndex);
            CreateTable(command, fullName);
        }
        public void CreateTable(SqlCommand command, DateTime valueTime) 
        {            
            int timeIndex = GetTimeIndex(valueTime);
            if (startTableIndex == 0 && endTableIndex == 0)
            {
                string fullName = GetTableName(valueTime);
                CreateTable(command, fullName);
                startTableIndex = timeIndex;
                endTableIndex = timeIndex;
                UpdateIndex(command);
            }
            else
            {
                if (timeIndex < startTableIndex)
                {
                    CreateFrontTable(command, timeIndex);
                }
                else if (timeIndex > endTableIndex)
                {
                    CreateBackTable(command, timeIndex);
                }
            }
        }
        public void CreateTable(SqlCommand command, DateTime startTime, DateTime endTime) 
        {
            int startIndex = GetTimeIndex(startTime);
            int endIndex = GetTimeIndex(endTime);
            if (startTableIndex == 0 && endTableIndex == 0)
            {
                for (int i = startIndex; i <= endIndex; i++)
                {
                    string fullName = GetTableName(tableName, i);
                    CreateTable(command, fullName);
                }
                startTableIndex = startIndex;
                endTableIndex = endIndex;
                UpdateIndex(command);
            }
            else
            {
                if (startIndex < startTableIndex)
                {
                    CreateFrontTable(command, startIndex);
                }
                if (endIndex < startTableIndex)
                {
                    CreateBackTable(command, endIndex);
                }
            }
        }

        private void CreateFrontTable(SqlCommand command, int timeIndex)
        {
            for (int i = timeIndex; i < startTableIndex; i++)
            {
                string fullName = GetTableName(tableName, i);
                CreateTable(command, fullName);                
            }
            startTableIndex = timeIndex;
            UpdateIndex(command);
        }
        private void CreateBackTable(SqlCommand command, int timeIndex)
        {
            for (int i = timeIndex; i > endTableIndex; i--)
            {
                string fullName = GetTableName(tableName, i);
                CreateTable(command, fullName);
            }
            endTableIndex = timeIndex;
            UpdateIndex(command);
        }
        private void CreateTable(SqlCommand command, string fullName)
        {
            string commandString = @"IF NOT EXISTS(SELECT 1 FROM sysobjects WHERE name ='{0}' AND type IN ('S','U'))
                                        CREATE TABLE [DBO].[{0}](
                                            [ObjectId] [INT] NOT NULL,
                                            [ValueType] [INT] NOT NULL,
                                            [ValueTime] [DATETIME] NOT NULL,
                                            [BinaryValue] [VARBINARY](MAX) NULL,
                                            [ImportTime] [DATETIME] NULL,
                                            [InterpolateTime] [DATETIME] NULL,
                                         CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED 
                                         (
                                             [ObjectId] ASC,
                                             [ValueType] ASC,
                                             [ValueTime] ASC
                                         )) ON [PRIMARY]";
            commandString = string.Format(commandString, fullName);
            command.CommandText = commandString;
            command.CommandType = CommandType.Text;
            command.ExecuteNonQuery();
            Console.WriteLine("[创建数据表]：" + fullName);
        }
        private int GetTimeIndex(DateTime valueTime)
        {
            return (valueTime.Year * 12 + valueTime.Month) / splitMonthStep;
        }
        private string GetTableName(string tableName, int tableIndex)
        {
            return tableName + tableIndex;
        }
    }
}
